{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Musicians - Easy\n",
    "\n",
    "## Tables used in the musicians database\n",
    "\n",
    "band(**band_no**, band_name, band_home, band_type, b_date, band_contact)\n",
    "\n",
    "composer(**comp_no**, comp_is, comp_type)\n",
    "\n",
    "composition(**c_no**, comp_date, c_title, c_in)\n",
    "\n",
    "concert(**concert_no**, concert_venue, concert_in, con_date, concert_orgniser)\n",
    "\n",
    "has_composed(**_cmpr_no_**, **_cmpn_no_**)\n",
    "\n",
    "musician(**m_no**, m_name, born, died, born_in, living_in)\n",
    "\n",
    "performance(**_pfrmnc_no_**, gave, performed, conducted_by, performed_in)\n",
    "\n",
    "performer(**perf_no**, perf_is, instrument, perf_type)\n",
    "\n",
    "place(**place_no**, place_town, place_country)\n",
    "\n",
    "plays_in(**_player_**, **band_id**)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- **musician**\n",
    "\n",
    "m_no | m_name | born | died | born_in | living_in\n",
    "-:|-----------|------|------|--------:|-----:\n",
    "1 | Fred Bloggs | 02/01/48 |  | 1 | 2\n",
    "2 | John Smith | 03/03/50 |  | 3 | 4\n",
    "3 | Helen Smyth | 08/08/48 |  | 4 | 5\n",
    "4 | Harriet Smithson | 09/05/1909 | 20/09/80 | 5 | 6\n",
    "5 | James First | 10/06/65 |  | 7 | 7\n",
    "... | | | | | \n",
    "\n",
    "- **place**\n",
    "\n",
    "place_no | place_town | place_country\n",
    "--------:|------------|---------\n",
    "1 | Manchester | England\n",
    "2 | Edinburgh | Scotland\n",
    "3 | Salzburg | Austria\n",
    "4 | New York | USA\n",
    "5 | Birmingham | England\n",
    "... | | \n",
    "\n",
    "- **performer**\n",
    "\n",
    "perf_no | perf_is | instrument | perf_type\n",
    "-:|--:|--------|---------------------\n",
    "1 | 2 | violin | classical\n",
    "2 | 4 | viola | classical\n",
    "3 | 6 | banjo | jazz\n",
    "4 | 8 | violin | classical\n",
    "5 | 12 | guitar | jazz\n",
    "...| | |\n",
    "\n",
    "- **composer**\n",
    "\n",
    "comp_no | comp_is | comp_type\n",
    "-:|--:|----------------\n",
    "1 | 1 | jazz\n",
    "2 | 3 | classical\n",
    "3 | 5 | jazz\n",
    "4 | 7 | classical\n",
    "5 | 9 | jazz\n",
    "... | |\n",
    "\n",
    "- **band**\n",
    "\n",
    "band_no | band_name | band_home | band_type | b_date | band_contact\n",
    "-:|-----|--:|-----------|----------|----:\n",
    "1 | ROP | 5 | classical | 30/01/01 | 11\n",
    "2 | AASO | 6 | classical |  | 10\n",
    "3 | The J Bs | 8 | jazz |  | 12\n",
    "4 | BBSO | 9 | classical |  | 21\n",
    "5 | The left Overs | 2 | jazz |  | 8\n",
    "... | | | | |\n",
    "\n",
    "- **plays_in**\n",
    "\n",
    "player | band_id\n",
    "-:|---:\n",
    "1 | 1\n",
    "1 | 7\n",
    "3 | 1\n",
    "4 | 1\n",
    "4 | 7\n",
    "... |\n",
    "\n",
    "- **composition**\n",
    "\n",
    "c_no | comp_date | c_title | c_in\n",
    "-:|----------|--------|-------:\n",
    "1 | 17/06/75 | Opus 1 | 1\n",
    "2 | 21/07/76 | Here Goes | 2\n",
    "3 | 14/12/81 | Valiant Knight | 3\n",
    "4 | 12/01/82 | Little Piece | 4\n",
    "5 | 13/03/85 | Simple Song | 5\n",
    "... | | |\n",
    "\n",
    "- **has_composed**\n",
    "\n",
    "cmpr_no | cmpn_no\n",
    "-:|---:\n",
    "1 | 1\n",
    "1 | 8\n",
    "2 | 11\n",
    "3 | 2\n",
    "3 | 13\n",
    "... |\n",
    "\n",
    "- **concert**\n",
    "\n",
    "concert_no | concert_venue | concert_in | con_date | concert_orgniser\n",
    "-:|------------------|--:|----------|----:\n",
    "1 | Bridgewater Hall | 1 | 06/01/95 | 21\n",
    "2 | Bridgewater Hall | 1 | 08/05/96 | 3\n",
    "3 | Usher Hall | 2 | 03/06/95 | 3\n",
    "4 | Assembly Rooms | 2 | 20/09/97 | 21\n",
    "5 | Festspiel Haus | 3 | 21/02/95 | 8\n",
    "... | | | |\n",
    "\n",
    "- **performance**\n",
    "\n",
    "pfrmnc_no | gave | performed | conducted_by | performed_in\n",
    "-:|--:|--:|---:|-----------------------:\n",
    "1 | 1 | 1 | 21 | 1\n",
    "2 | 1 | 3 | 21 | 1\n",
    "3 | 1 | 5 | 21 | 1\n",
    "4 | 1 | 2 | 1 | 2\n",
    "5 | 2 | 4 | 21 | 2\n",
    "... | | | |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is the data model of the musician tables\n",
    "\n",
    "![](../src/img/musician_str.svg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ·········\n"
     ]
    }
   ],
   "source": [
    "# Prerequesites\n",
    "import getpass\n",
    "%load_ext sql\n",
    "pwd = getpass.getpass()\n",
    "# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo\n",
    "%sql postgresql://postgres:$pwd@localhost/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "**Give the organiser's name of the concert in the Assembly Rooms after the first of Feb, 1997.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>m_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>James Steeple</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('James Steeple',)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT musician.m_name\n",
    "  FROM concert JOIN musician ON (concert.concert_orgniser=musician.m_no)\n",
    "    WHERE concert_venue='Assembly Rooms' AND con_date>'1997-02-01'::date;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "**Find all the performers who played guitar or violin and were born in England.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "6 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>m_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Alan Fluff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Davis Heavan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Harriet Smithson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Harry Forte</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>James First</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Theo Mengel</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Alan Fluff',),\n",
       " ('Davis Heavan',),\n",
       " ('Harriet Smithson',),\n",
       " ('Harry Forte',),\n",
       " ('James First',),\n",
       " ('Theo Mengel',)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT musician.m_name\n",
    "  FROM performer JOIN musician ON (\n",
    "    performer.perf_is=musician.m_no) JOIN\n",
    "    place ON (musician.born_in=place.place_no)\n",
    "    WHERE performer.instrument IN ('violin', 'guitar') AND\n",
    "      place.place_country='England'\n",
    "    ORDEr BY musician.m_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "**List the names of musicians who have conducted concerts in USA together with the towns and dates of these concerts.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>m_name</th>\n",
       "        <th>place_town</th>\n",
       "        <th>con_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>James Steeple</td>\n",
       "        <td>New York</td>\n",
       "        <td>1997-06-15</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('James Steeple', 'New York', datetime.date(1997, 6, 15))]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT musician.m_name, place_town, con_date\n",
    "  FROM concert JOIN place ON (concert.concert_in=place.place_no)\n",
    "    JOIN performance ON (performance.performed_in=concert.concert_no)\n",
    "    JOIN musician ON (musician.m_no=performance.conducted_by)\n",
    "    WHERE place_country='USA'\n",
    "    ORDER BY musician.m_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "**How many concerts have featured at least one composition by Andy Jones? List concert date, venue and the composition's title.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>con_date</th>\n",
       "        <th>concert_venue</th>\n",
       "        <th>c_title</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1996-05-08</td>\n",
       "        <td>Bridgewater Hall</td>\n",
       "        <td>A Simple Piece</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(1996, 5, 8), 'Bridgewater Hall', 'A Simple Piece')]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT con_date, concert_venue, c_title\n",
    "  FROM concert JOIN composition ON (concert.concert_no=composition.c_in) JOIN\n",
    "    has_composed ON (composition.c_no=has_composed.cmpn_no) JOIN\n",
    "    composer ON (composer.comp_no=has_composed.cmpr_no) JOIN\n",
    "    musician ON (musician.m_no=composer.comp_is)\n",
    "    WHERE musician.m_name='Andy Jones';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "**List the different instruments played by the musicians and avg number of musicians who play the instrument.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "29 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>m_name</th>\n",
       "        <th>instrument</th>\n",
       "        <th>avg</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Alan Fluff</td>\n",
       "        <td>viola</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Alan Fluff</td>\n",
       "        <td>violin</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Davis Heavan</td>\n",
       "        <td>cello</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Davis Heavan</td>\n",
       "        <td>guitar</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Davis Heavan</td>\n",
       "        <td>viola</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Elsie James</td>\n",
       "        <td>cello</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Elsie James</td>\n",
       "        <td>guitar</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Elsie James</td>\n",
       "        <td>viola</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Harriet Smithson</td>\n",
       "        <td>viola</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Harriet Smithson</td>\n",
       "        <td>violin</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Harry Forte</td>\n",
       "        <td>clarinet</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Harry Forte</td>\n",
       "        <td>drums</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Harry Forte</td>\n",
       "        <td>violin</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Helen Smyth</td>\n",
       "        <td>horn</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>James First</td>\n",
       "        <td>violin</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>James Quick</td>\n",
       "        <td>flute</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>James Quick</td>\n",
       "        <td>trumpet</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jeff Dawn</td>\n",
       "        <td>bass</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jeff Dawn</td>\n",
       "        <td>violin</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John Smith</td>\n",
       "        <td>bass</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">29 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Alan Fluff', 'viola', 4),\n",
       " ('Alan Fluff', 'violin', 7),\n",
       " ('Davis Heavan', 'cello', 3),\n",
       " ('Davis Heavan', 'guitar', 2),\n",
       " ('Davis Heavan', 'viola', 4),\n",
       " ('Elsie James', 'cello', 3),\n",
       " ('Elsie James', 'guitar', 2),\n",
       " ('Elsie James', 'viola', 4),\n",
       " ('Harriet Smithson', 'viola', 4),\n",
       " ('Harriet Smithson', 'violin', 7),\n",
       " ('Harry Forte', 'clarinet', 1),\n",
       " ('Harry Forte', 'drums', 2),\n",
       " ('Harry Forte', 'violin', 7),\n",
       " ('Helen Smyth', 'horn', 1),\n",
       " ('James First', 'violin', 7),\n",
       " ('James Quick', 'flute', 2),\n",
       " ('James Quick', 'trumpet', 1),\n",
       " ('Jeff Dawn', 'bass', 3),\n",
       " ('Jeff Dawn', 'violin', 7),\n",
       " ('John Smith', 'bass', 3),\n",
       " ('John Smith', 'flute', 2),\n",
       " ('John Smith', 'violin', 7),\n",
       " ('Louise Simpson', 'bass', 3),\n",
       " ('Louise Simpson', 'cornet', 1),\n",
       " ('Louise Simpson', 'trombone', 1),\n",
       " ('Sue Little', 'cello', 3),\n",
       " ('Theo Mengel', 'banjo', 1),\n",
       " ('Theo Mengel', 'drums', 2),\n",
       " ('Theo Mengel', 'violin', 7)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "    SELECT m_name, instrument\n",
    "      FROM performer JOIN musician ON (performer.perf_is=musician.m_no)\n",
    "        ORDER BY m_name\n",
    ")\n",
    "SELECT t.m_name, t.instrument, a.avg\n",
    "  FROM t JOIN (SELECT instrument, COUNT(*) avg FROM t GROUP BY instrument) a ON (\n",
    "    t.instrument=a.instrument)\n",
    "    ORDER BY t.m_name, t.instrument;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
